﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Risk.Model;

namespace Risk.Services
{
    public static class DatabaseManager
    {
        private static SqlConnection StartConnection()
        {
            var connection = new SqlConnection(@"server=FREDERIC-PC\SQL;database=Risk;User ID=riskserver;Password=internal");
            connection.Open();
            return connection;
        }
        private static SqlCommand CreateCommand(SqlConnection connection, String command, params Object[] parameters)
        {
            return new SqlCommand(String.Format(command, parameters), connection);
        }

        public static bool CheckUserCredentials(string username, string password)
        {
            using (var connection = StartConnection())
            {
                var command = CreateCommand(connection, @"SELECT * FROM Credential WHERE Username = '{0}' AND Password = '{1}'",
                                            username, password);
                using (var reader = command.ExecuteReader())
                    return reader.HasRows;
            }
        }
        public static bool RegisterUser(string username, string password)
        {
                try
                {
                    using (var connection = StartConnection())
                    {
                        CreateCommand(connection, @"INSERT INTO Credential (Username, Password)" +
                                                  "VALUES ('{0}','{1}')", username, password)
                            .ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    return false;
                }

                return true;
        }

        public static int CreateGame()
        {
            using (var connection = StartConnection())
            {
                return Convert.ToInt32(CreateCommand(connection, @"INSERT INTO Game (WinnerName) VALUES(NULL); SELECT IDENT_CURRENT('Game');")
                            .ExecuteScalar());
            }
        }

        public static void CreatePlayer(int gameId, string username, bool isCreator=false)
        {
            using (var connection = StartConnection())
            {
                int riskId = isCreator
                                 ? 0
                                 : Convert.ToInt32(
                                     CreateCommand(connection,
                                         "SELECT MAX(Player.RiskId) FROM Player JOIN PlayerGame ON PlayerGame.PlayerId = Player.Id WHERE PlayerGame.GameId = '{0}'",
                                         gameId).ExecuteScalar()) + 1;
                CreateCommand(connection, @"INSERT INTO Player (Username, RiskId) VALUES('{0}', '{1}')", username, riskId)
                    .ExecuteNonQuery();
                int playerId = Convert.ToInt32(CreateCommand(connection, @"SELECT IDENT_CURRENT('Player');").ExecuteScalar());
                CreateCommand(connection, @"INSERT INTO PlayerGame (PlayerId, GameId, IsCreator) VALUES('{0}','{1}', '{2}')",
                              playerId, gameId, isCreator.ToString()).ExecuteNonQuery();
            }
        }
        public static String GetPlayerName(int playerId)
        {
            using (var connection = StartConnection())
                return Convert.ToString(CreateCommand(connection, @"SELECT Username FROM Player WHERE Id='{0}'", playerId).ExecuteScalar());
        }
        public static String GetPlayerColor(int playerId)
        {
            using (var connection = StartConnection())
                return Convert.ToString(CreateCommand(connection, @"SELECT Color FROM Player WHERE Id='{0}'", playerId).ExecuteScalar());
        }
        public static int GetPlayerState(int playerId)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, @"SELECT State FROM Player WHERE Id='{0}'", playerId).ExecuteScalar());
        }
        public static List<int> GetOpenGameIds()
        {
            using (var connection = StartConnection())
            {
                var idList = new List<int>();
                var command = CreateCommand(connection, @"SELECT Id FROM Game WHERE StartTime IS NULL");

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                        idList.Add(Convert.ToInt32(reader["Id"]));
                }
                return idList;
            }
        }
        public static List<int> GetPlayerIds(int gameId)
        {
            using (var connection = StartConnection())
            {
                var idList = new List<int>();

                var command = CreateCommand(connection, @"SELECT PlayerId FROM PlayerGame WHERE GameId='{0}'", gameId);

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                        idList.Add(Convert.ToInt32(reader["PlayerId"]));
                }
                return idList;
            }
        }
        public static bool PlayerIsIngame(string username, int gameid)
        {
            using (var connection = StartConnection())
            {
                var command =
                    CreateCommand(connection,
                                  "SELECT Player.Id FROM Player JOIN PlayerGame ON PlayerGame.PlayerId = Player.Id WHERE PlayerGame.GameId = '{0}' AND Player.Username = '{1}'",
                                  gameid, username);
                using (var reader = command.ExecuteReader())
                {
                    return reader.HasRows;
                }
            }
        }
        public static bool IsCreatorOfGame(string username, int gameid)
        {
            using (var connection = StartConnection())
            {
                var command =
                    CreateCommand(connection,
                                  "SELECT Player.Id FROM Player JOIN PlayerGame ON PlayerGame.PlayerId = Player.Id WHERE PlayerGame.GameId = '{0}' AND Player.Username = '{1}' AND PlayerGame.IsCreator='True'",
                                  gameid, username);
                using (var reader = command.ExecuteReader())
                    return reader.HasRows;
            }
        }


        public static void SetPlayerColor(int playerId, string color)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Player SET Color = '{0}' WHERE Id='{1}'", color, playerId).ExecuteNonQuery();
        }

        public static bool GameHasStarted(int id)
        {
            using (var connection = StartConnection())
                return CreateCommand(connection, "SELECT * FROM Game WHERE Game.Id='{0}' AND Game.StartTime IS NOT NULL", id).ExecuteReader().HasRows;
        }

        public static int GetArmyId(int playerId)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Army.Id FROM Army WHERE Army.PlayerId='{0}'", playerId).ExecuteScalar());
        }
        public static int GetArmyReserveFromPlayer(int playerId)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Army.Reserve FROM Army WHERE Army.PlayerId='{0}'", playerId).ExecuteScalar());
        }
        public static void AddTroops(Game game, Army army)
        {
            using (var connection = StartConnection())
            {
                var command = CreateCommand(connection, @"SELECT * FROM Troop WHERE ArmyId='{0}'", army.Id);

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {

                        int id = Convert.ToInt32(reader["Id"]);
                        int count = Convert.ToInt32(reader["Count"]);
                        string regionId = Convert.ToString(reader["RegionId"]);
                        Region region = game.UnassignedRegions.Find(r => r.Id == regionId);
                        game.UnassignedRegions.Remove(region);
                        army.TroopGroup.Add(new Troop(id, count, region));
                    }
                }
            }
        }

        public static Game LoadGame(int gameId)
        {
            var game = new Game();
            var playerIds = GetPlayerIds(gameId);
            //door te sorteren op playerId heeft player[i] ook RiskId = i, zelfde met armies[i]
            playerIds.Sort();
            foreach (int id in playerIds)
            {
                String username = GetPlayerName(id);
                int playerState = GetPlayerState(id);

                var player = new Player(id, username, playerState);
                game.Players.Add(player);

                int armyId = GetArmyId(id);
                int armyReserve = GetArmyReserveFromPlayer(id);
                var army = new Army(player, armyId, armyReserve);
                game.Armies.Add(army);
                AddTroops(game, army);
            }
            return game;
        }

        public static List<Player> GetPlayers(int gameId)
        {
            using (var connection = StartConnection())
            {
                var playerList = new List<Player>();

                var command = CreateCommand(connection,
                                            @"SELECT * FROM Player JOIN PlayerGame ON PlayerGame.PlayerId = Player.Id WHERE PlayerGame.GameId = '{0}'",
                                            gameId);


                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = Convert.ToInt32(reader["Id"]);
                        var state = Convert.ToInt32(reader["State"]);
                        string name = Convert.ToString(reader["Username"]);
                        playerList.Add(new Player(id, name, state));
                    }
                }
                return playerList;
            }
        }

        public static int GetGameDuration(int gameId)
        {
            using (var connection = StartConnection())
            {
                using ( var reader = CreateCommand(connection, @"SELECT Game.StartTime, Game.EndTime FROM Game WHERE Game.Id='{0}'",
                                      gameId).ExecuteReader())
                {
                    reader.Read();
                    return ((int)( DateTime.Parse(reader["EndTime"].ToString()) - DateTime.Parse(reader["StartTime"].ToString()) ).TotalMinutes);
                }
            }
        }
        public static void EndGame(int gameId, string winnerName)
        {
            using (var connection = StartConnection())
            {
                CreateCommand(connection, @"UPDATE Game SET Game.EndTime='{0}', Game.WinnerName='{2}' WHERE Game.Id='{1}'", DateTime.Now.ToString("yyyy-MM-ddThh:mm:ss"), gameId, winnerName).ExecuteNonQuery();
            }
        }
        public static void LoadGameIntoDatabase(Game game, int gameId)
        {
            using (var connection = StartConnection())
            {
                //set game start time
                CreateCommand(connection, @"UPDATE Game SET Game.StartTime='{0}' WHERE Game.Id='{1}'", DateTime.Now.ToString("yyyy-MM-ddThh:mm:ss"), gameId).ExecuteNonQuery();
                foreach (var army in game.Armies)
                {
                    CreateCommand(connection, @"INSERT INTO Army (PlayerId, Reserve, Bonus) VALUES('{0}', '{1}', '{2}')",
                                  army.Player.Id, army.Reserve, army.Bonus).ExecuteNonQuery();
                    army.Id =
                        Convert.ToInt32(CreateCommand(connection, @"SELECT IDENT_CURRENT('Army');").ExecuteScalar());
                    CreateCommand(connection, @"INSERT INTO GameArmy (GameId, ArmyId) VALUES('{0}', '{1}')", gameId,
                                  army.Id).ExecuteNonQuery();
                    foreach (Troop t in army.TroopGroup)
                    {
                        CreateCommand(connection,
                                      @"INSERT INTO Troop (ArmyId, RegionId, Count) VALUES('{0}', '{1}', '{2}')",
                                      army.Id, t.Region.Id, t.TroopSize).ExecuteNonQuery();
                        t.Id =
                            Convert.ToInt32(CreateCommand(connection, @"SELECT IDENT_CURRENT('Troop');").ExecuteScalar());

                    }
                }
            }
        }


        public static List<int> GetTroopIds(int armyId)
        {
            using (var connection = StartConnection())
            {
                var idList = new List<int>();
                var command = CreateCommand(connection, @"SELECT Id FROM Troop WHERE ArmyId='{0}'", armyId);

                using (var reader = command.ExecuteReader())
                    while (reader.Read())
                        idList.Add(Convert.ToInt32(reader["Id"]));
                return idList;
            }
        }
        public static List<int> ArmyIds(int id)
        {
            using (var connection = StartConnection())
            {
                var idList = new List<int>();
                var command = CreateCommand(connection, @"SELECT ArmyId FROM GameArmy WHERE GameId='{0}'", id);

                using (var reader = command.ExecuteReader())
                    while (reader.Read())
                        idList.Add(Convert.ToInt32(reader["ArmyId"]));
                return idList;
            }
        }
        public static int GetPlayerId(int armyId)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Army.PlayerId FROM Army WHERE Army.Id = '{0}'", armyId).ExecuteScalar());
        }

        public static int GetArmyReserve(int id)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Army.Reserve FROM Army WHERE Army.Id='{0}'", id).ExecuteScalar());
        }
        public static int GetArmyBonus(int id)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Army.Bonus FROM Army WHERE Army.Id='{0}'", id).ExecuteScalar());
        }

        public static String GetRegion(int troopId)
        {
            using (var connection = StartConnection())
                return Convert.ToString(CreateCommand(connection, "SELECT Troop.RegionId FROM Troop WHERE Troop.Id='{0}'", troopId).ExecuteScalar());
        }
        public static int GetCount(int troopId)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Troop.Count FROM Troop WHERE Troop.Id='{0}'", troopId).ExecuteScalar());
        }
        public static int GetPlayerRiskId(int uniqueId)
        {
            using (var connection = StartConnection())
                return Convert.ToInt32(CreateCommand(connection, "SELECT Player.RiskId FROM Player WHERE Player.Id = '{0}'", uniqueId).ExecuteScalar());
        }
        public static int GetPlayerRiskId(string username)
        {
            using (var connection = StartConnection())
                return  Convert.ToInt32(CreateCommand(connection, "SELECT Player.RiskID FROM Player WHERE Player.Username = '{0}'", username).ExecuteScalar());
        }

        public static void SetPlayerState(int value, int uniquePlayerId)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Player SET State = '{0}' WHERE Id='{1}'", value, uniquePlayerId).ExecuteNonQuery();
        }

        public static void SetArmyReserve(int value, int armyId)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Army SET Reserve = '{0}' WHERE Id='{1}'", value, armyId).ExecuteNonQuery();
        }

        public static void SetArmyBonus(int value, int armyId)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Army SET Bonus = '{0}' WHERE Id='{1}'", value, armyId).ExecuteNonQuery();
        }

        public static void SetTroopCount(int value, int id)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Troop SET Count = '{0}' WHERE Id='{1}'", value, id).ExecuteNonQuery();
        }

        public static void SetTroopRegion(string value, int id)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Troop SET Region = '{0}' WHERE Id='{1}'", value, id).ExecuteNonQuery();
        }

        public static void SetTroopArmy(int value, int id)
        {
            using (var connection = StartConnection())
                CreateCommand(connection, "UPDATE Troop SET ArmyId = '{0}' WHERE Id='{1}'", value, id).ExecuteNonQuery();
        }
        public static List<int> GetActiveGameIds(string username)
        {
            using (var connection = StartConnection())
            {
                var idList = new List<int>();
                var command = CreateCommand(connection,
                    "SELECT Game.Id FROM Game " + 
                    "JOIN PlayerGame ON PlayerGame.GameId = Game.Id " + 
                    "JOIN Player ON Player.Id = PlayerGame.PlayerId " + 
                    "WHERE Game.StartTime IS NOT NULL AND Player.Username = '{0}'", username);

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                        idList.Add(Convert.ToInt32(reader["Id"]));
                }
                return idList;
            }
        }


        public static bool IsWinner(int uniqueId)
        {
            using (var connection = StartConnection())
            {
                var command = CreateCommand(connection, @"SELECT * FROM PlayerGame " + "" +
                                                        "JOIN Game ON PlayerGame.GameId = Game.Id " + 
                                                        "JOIN Player ON Player.Id = PlayerGame.PlayerId " + 
                                                        "WHERE Player.Id ='{0}' AND " + 
                                                        "Game.WinnerName = Player.Username", uniqueId);
                using (var reader = command.ExecuteReader())
                    return reader.HasRows;
            }
        }

        public static List<int> GetFinishedGameIds()
        {
            using (var connection = StartConnection())
            {
                var idList = new List<int>();
                var command = CreateCommand(connection,
                    "SELECT Game.Id FROM Game WHERE Game.WinnerName IS NOT NULL");

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                        idList.Add(Convert.ToInt32(reader["Id"]));
                }
                return idList;
            }
        }
    }
}
